python db api

Two main objects: connection and cursor

connection

connection = Connect('databasename', 'username', 'password')

connection object methods:

.cursor() - returns a new cursor object using the connection
.commit() - commits any pending transaction to the database
.rollback() - causes the database to roll back to the start of any pending transaction
.close() - closes the database connection

cursor

  • a control structure that enables traversal over the records in a database
  • behaves like a file name or file handle in a programming language
cursor = connection.cursor()
cursor.execute("select * from mytable limit 10")
results = cursor.fetchall()
cursor.close()
connection.close()

cursor object methods:

.execute() -
.executemany() -
.fetchone() -
.fetchmany() -
.fetchall() -
.nextset() -
.Arraysize() -
.close() -

sqlite3

built-in

ibm_db

dsn = (
    "DRIVER={IBM DB2 ODBC DRIVER};"
    "DATABASE=BLUDB;"
    "HOSTNAME=dashdb-xxx-xxxx-xx-lon02-06.services.eu-gb.bluemix.net;"
    "PORT=50000;"
    "PROTOCOL=TCPIP;"
    "UID=xxx99999;"
    "PWD=xxxxxxxxxxxxxxxx;"
)
try:
    conn = ibm_db.connect(dsn, "", "")
except:
    print ("Unable to connect: ", ibm_db.conn_errormsg() )

server = ibm_db.server_info(conn)
print ("DBMS_NAME: ", server.DBMS_NAME)
print ("DBMS_VER:  ", server.DBMS_VER)
print ("DB_NAME:   ", server.DB_NAME)

client = ibm_db.client_info(conn)
print ("DRIVER_NAME:          ", client.DRIVER_NAME) 
print ("DRIVER_VER:           ", client.DRIVER_VER)
print ("DATA_SOURCE_NAME:     ", client.DATA_SOURCE_NAME)
print ("DRIVER_ODBC_VER:      ", client.DRIVER_ODBC_VER)
print ("ODBC_VER:             ", client.ODBC_VER)
print ("ODBC_SQL_CONFORMANCE: ", client.ODBC_SQL_CONFORMANCE)
print ("APPL_CODEPAGE:        ", client.APPL_CODEPAGE)
print ("CONN_CODEPAGE:        ", client.CONN_CODEPAGE)

Use the connection object to run queries

stmt = ibm_db.exec_immediate(conn, "select * from table limit 10")
ibm_db.fetch_both(stmt)

Use the connection to create another connection object that can be used with pandas

import pandas as pd
import ibm_db_dbi
pconn = ibm_db_dbi.Connection(conn)
df = pd.read_sql("select * from table limit 10", pconn)